package bigdata;

import java.sql.*;


/*
 * the SqlDB class creates and handles our user data base
 */

public class SqlDB {

	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost/test";

	// Database credentials
	static final String USER = "root";
	static final String PASS = "1234";
	static Connection conn = null;

//	public static void main(String[] args) {
//
//		runDB();
//		insertWeight("sad-123-asd", 2.5);
//
//		closeDB();
//
//	}

	public static void runDB() {

		//Register JDBC driver
		try {
			Class.forName("com.mysql.jdbc.Driver");

			//Open a connection
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
			
		} catch (ClassNotFoundException e) {
			SimulationMain.gui.addStringToLog(e.toString());
			e.printStackTrace();
		} catch (SQLException e) {
			SimulationMain.gui.addStringToLog(e.toString());
			e.printStackTrace();
		}

	}

	public static boolean updateVisitor(VisitData visit) {

		Statement stmt;
		try {
			stmt = conn.createStatement();

			// our SQL SELECT query.
			// if you only need a few columns, specify them by name instead of
			// using "*"

			String sql = "UPDATE online_customer_db SET type='"+visit.getType()+"',score=" + visit.getScore()
					+ " WHERE customer_id='" + visit.visitId + "';";

			stmt.executeUpdate(sql);

			stmt.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		return true;
	}

	public static boolean closeDB() {

		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		return true;
	}

	public static boolean cleanCustomersTable() {

		Statement stmt;
		try {
			stmt = conn.createStatement();

			String sql = "delete from online_customer_db;";

			stmt.executeUpdate(sql);

			stmt.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		return true;

	}
	
	public static boolean insertCustomer(String customer_id) {

		Statement stmt;
		try {
			stmt = conn.createStatement();

			String sql = "INSERT INTO online_customer_db(customer_id)"
					+ "VALUES('" + customer_id + "')";

			stmt.executeUpdate(sql);

			stmt.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		return true;

	}

	public static boolean checkIfExists(String customer_id) {
		Statement stmt;
		try {
			stmt = conn.createStatement();

			// our SQL SELECT query.
			// if you only need a few columns, specify them by name instead of
			// using "*"
			String query = "SELECT * FROM online_customer_db WHERE customer_id='"
					+ customer_id + "';";

			// execute the query, and get a java resultset
			ResultSet rs = stmt.executeQuery(query);
			if (!rs.next()) {

				stmt.close();
				rs.close();

				return false;

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		}

		return true;

	}
	
	public static VisitData[] getCustomersList()
	{
		
		VisitData[] data;
		Statement stmt;
		
		try {
			stmt = conn.createStatement();

			String query = "SELECT * FROM online_customer_db order by score desc;";

			// execute the query, and get a java resultset
			ResultSet rs = stmt.executeQuery(query);
			
			int rowcount = 0;
			int currentRow = 0;
			
			if (rs.last()) {
			  rowcount = rs.getRow();
			  rs.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
			}
			
			data = new VisitData[rowcount];
			while (rs.next()) {
				data[currentRow] = new VisitData(rs.getString("customer_id"));
				data[currentRow].setRate(rs.getInt("rate"));
				data[currentRow].setScore(rs.getInt("score"));
				data[currentRow].setType(rs.getString("type"));
				currentRow++;
			}
				

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

		return data;
	}
	
	public static int getRate(String customer_id)
	{
		
		int rate = 0;
		Statement stmt;
		try {
			stmt = conn.createStatement();

			
			String query = "SELECT rate FROM online_customer_db WHERE customer_id='"
					+ customer_id + "';";

			// execute the query, and get a java resultset
			ResultSet rs = stmt.executeQuery(query);
			if (rs.next())
				rate= rs.getInt("rate");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}

		return rate;
	}
	
	
	public static boolean setRate(String customer_id,int rate)
	{
		
		
		Statement stmt;
		try {
			stmt = conn.createStatement();

			String sql = "UPDATE online_customer_db SET rate=" + rate
					+ " WHERE customer_id='" + customer_id + "';";

			stmt.executeUpdate(sql);

			stmt.close();


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		return true;
	}
	
	
	public static float getScore(String customer_id)
	{
		
		float score = 0;
		Statement stmt;
		try {
			stmt = conn.createStatement();

			
			String query = "SELECT score FROM online_customer_db WHERE customer_id='"
					+ customer_id + "';";

			// execute the query, and get a java resultset
			ResultSet rs = stmt.executeQuery(query);
			if (rs.next())
				score= rs.getFloat("score");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}

		return score;
	}
	
	
	public static boolean setScore(String customer_id,float score)
	{
		
		
		Statement stmt;
		try {
			stmt = conn.createStatement();

			String sql = "UPDATE online_customer_db SET score=" + score
					+ " WHERE customer_id='" + customer_id + "';";

			stmt.executeUpdate(sql);

			stmt.close();


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

		return true;
	}


}
